Excel Sheet Cell Listbox

Hello,

creating an Excel Export Sheet by Doors I would like to set up cells as listboxes..

Doing this with VBA the minimized macro code will be as follows:

    With Selection.Validation
        .Add Type:=xlValidateList, Formula1:= "open, clarify, closed"
    End With

Translating this to Doors OLE you will start with:

    OleAutoObj objCell = getCellOLE( objSheet, x, y )
    OleAutoObj objVali = null   
    string sValues[] = { "open, clarify, closed" }

    oleGet( objCell, "Validation", objVali )
    olePut( objVali, "Type", xlValidateList )
    olePut( objVali, "Formula1", sValues )

But, this won't work. Does anybody know why? Ideas?

Thanks in advance.

STB
 

Re: Excel Sheet Cell Listbox
Doug.Zawacki - Tue Jan 28 12:35:25 EST 2014

Hi,

Rather than trying to explain what is not going on with your code I thought I'd just publish the function that I have written to do exactly what you are trying to do. Please look at it closely to understand it. Good luck

bool oleExcel_Range_Validation_Set(int iValidationType,int iAlertStyle, int iValidationConditionOperator, string strFormula1, string strFormula2, strInputTitle,strInputMessage,strErrorTitle,strErrorMessage,bool bErrorAlerts )
{
   if (! oleExcel__CheckResult (oleGet (objRange, cPropertyValidation, objValidation)))
   {
      return(false)
   }

   clear(args)
   put(args, cPropertyValidationType, iValidationType)
   put(args, cPropertyAlertStyle, iAlertStyle )
   put(args, cPropertyConditionOperator,iValidationConditionOperator )
   put(args, cPropertyFormula1,strFormula1 )
   put(args, cPropertyFormula2,strFormula2 )

   if (! oleExcel__CheckResult (oleMethod (objValidation, cMethodAdd, args)))
   {
      return(false)
   }

   olePut(objValidation, cPropertyIgnoreBlank, false)
  
   if (! null strInputTitle)
   {
      olePut(objValidation, cPropertyInputTitle, strInputTitle)
   }
  
   if (! null strInputMessage)
   {
      olePut(objValidation, cPropertyInputMessage, strInputMessage)
   }
  
   if (! null strErrorTitle)
   {
      olePut(objValidation, cPropertyErrorTitle, strErrorTitle)
   }
  
   if (! null strErrorMessage)
   {
      olePut(objValidation, cPropertyErrorMessage, strErrorMessage)
   }

   // Set Error Alerts
   olePut(objValidation, cPropertyShowError, bErrorAlerts)

  
   return(true)
}

Re: Excel Sheet Cell Listbox
Molgy - Wed Jan 29 04:00:20 EST 2014

Hello Doug,

many thanks for you answer. It tried your example, but I get still an error message: "Method: Problem with OLE Argument names."

So, I have done something wrong. I would like to ask you, for more details about the arguments you put into the method:

string cPropertyValidationType = ?          "Type"   is wrong.

int iValidationType = ?                                  "3"   ?

int iValidationConditionOperator = ?

string strFormula1 = ?                             string array ?

string strFormula2 = ?

Thanks in advance.

STB

Re: Excel Sheet Cell Listbox
Doug.Zawacki - Wed Jan 29 10:45:06 EST 2014

Hi Molgy,

I'm not sure if you understand the OLE Object Model. Here is a link that has proved to be valuable to me:

http://msdn.microsoft.com/en-us/library/office/ff194068.aspx

Here is an example call to the validation function provided above:

oleExcel_Range_Validation_Set(xlValidateList,xlValidAlertStop, xlBetween, "="strValidationDataWorksheetName"!A1:A22", "","Input Title","Please Select Something","Error Title","Invalid Selection",bErrorAlerts)
 

Here are some constants that may help.

///////////////////////
// VALIDATION ALERT STYLES ---- XIDVAlertStyle Constants for Validation Object
///////////////////////
const int xlValidAlertStop          = 1 // Stop ICON
const int xlValidAlertWarning       = 2 // Warning ICON
const int xlValidAlertInformation   = 3 // Information ICON

///////////////////////
// VALIDATION TYPES ---- XIDVType Constants for Validation Object
///////////////////////
const int xlValidateInputOnly    = 0 //Validate only when user changes the value.
const int xlValidateWholeNumber  = 1 //Whole numeric values
const int xlValidateDecimal      = 2 //Numeric values.
const int xlValidateList         = 3 //Value must be present in a specified list.
const int xlValidateDate         = 4 //Date values.
const int xlValidateTime         = 5 //Time values.
const int xlValidateTextLength   = 6 //Length of text.
const int xlValidateCustom       = 7 //Data is validated using an arbitrary formula.
 
///////////////////////
// VALIDATION CONDITION OPERATORS ---- XlFormatConditionOperator
///////////////////////
const int xlBetween              = 1
const int xlNotBetween           = 2
const int xlEqual                = 3
const int xlNotEqual             = 4
const int xlGreater              = 5
const int xlLess                 = 6
const int xlGreaterEqual         = 7

const int xlLessEqual            = 8

Re: Excel Sheet Cell Listbox
Molgy - Tue Feb 04 02:58:10 EST 2014

Hello Doug,

thanks for your support. But, somethings is still wrong.

Here is a tiny test program:

OleAutoArgs args = create()

OleAutoObj objExcel = oleCreateAutoObject "Excel.Application"
OleAutoObj objSheet = null
OleAutoObj objWrkBk = null
OleAutoObj objWrkBks = null

OleAutoObj objRange = null
OleAutoObj objVali = null

string sRange = "B2"
//string strFormula = "=" "open, clarify, closed"
string strFormula = "open, clarify, closed"

// create Excel Sheet
olePut( objExcel, "Visible", true )
oleGet( objExcel, "Workbooks", objWrkBks )
put( args, 1 )
oleMethod( objWrkBks, "Add", args, objWrkBk )
oleGet( objExcel, "ActiveSheet", objSheet )

// get Cell
clear( args )
put( args, sRange )
oleGet( objSheet, "Range", args, objRange )
oleMethod( objRange, "Select" )

// set Cell
print "\nVali:  " oleGet( objRange, "Validation", objVali )
clear( args )
put( args, "Type", 3 )
put( args, "AlertStyle", 1 )
put( args, "ConditionOperator", 1 )
put( args, "Formula1", strFormula )
put( args, "Formula2", "" )

print "\nMethod: " oleMethod ( objVali, "Add", args )

So, I would like to ask you to test this. Where is the bug? Thanks in advance.

STB

Re: Excel Sheet Cell Listbox
Doug.Zawacki - Tue Feb 04 07:33:53 EST 2014

Try "Operator" instead of "ConditionOperator"

Re: Excel Sheet Cell Listbox
Molgy - Tue Feb 04 08:54:18 EST 2014

Many Thanks. It works.

Please try the code with this line: string strFormula = "open; clarify; closed"

STB